<!DOCTYPE html>
<html lang="zh-CN">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>数学周测成绩分析</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
    <script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
    <style>
        * {
            margin: 0;
            padding: 0;
            box-sizing: border-box;
            font-family: 'Segoe UI', 'Microsoft YaHei', sans-serif;
        }

        :root {
            --primary: #4F46E5;
            --primary-dark: #4338CA;
            --secondary: #10B981;
            --tertiary: #F59E0B;
            --light: #F9FAFB;
            --dark: #1F2937;
            --gray: #6B7280;
            --light-gray: #E5E7EB;
            --border: #D1D5DB;
            --card-shadow: 0 4px 12px rgba(0, 0, 0, 0.08);
            --success: #10B981;
            --warning: #F59E0B;
            --danger: #EF4444;
        }

        body {
            background: linear-gradient(135deg, #f0f7ff 0%, #f8fafc 100%);
            color: var(--dark);
            min-height: 100vh;
            padding: 20px;
        }

        .container {
            /* max-width: 1200px; */
            margin: 0 auto;
        }

        header {
            background: white;
            border-radius: 16px;
            box-shadow: var(--card-shadow);
            padding: 20px 30px;
            margin-bottom: 30px;
            display: flex;
            flex-direction: column;
            gap: 20px;
        }

        .header-top {
            display: flex;
            justify-content: space-between;
            align-items: center;
            flex-wrap: wrap;
            gap: 20px;
        }

        .logo {
            display: flex;
            align-items: center;
            gap: 12px;
            font-size: 1.5rem;
            font-weight: 700;
            color: var(--primary);
        }

        .logo i {
            font-size: 1.8rem;
        }

        .header-controls {
            display: flex;
            gap: 15px;
            flex-wrap: wrap;
        }

        .control-group {
            display: flex;
            flex-wrap: wrap;
            gap: 15px;
            margin-top: 10px;
        }

        .form-group {
            display: flex;
            flex-direction: column;
            min-width: 200px;
        }

        .form-group label {
            margin-bottom: 8px;
            font-weight: 500;
            color: var(--dark);
        }

        .form-group input,
        .form-group select {
            padding: 12px 15px;
            border-radius: 8px;
            border: 1px solid var(--border);
            background: white;
            font-size: 1rem;
            transition: all 0.3s ease;
        }

        .form-group input:focus,
        .form-group select:focus {
            outline: none;
            border-color: var(--primary);
            box-shadow: 0 0 0 3px rgba(79, 70, 229, 0.2);
        }

        .file-upload {
            position: relative;
            display: inline-block;
        }

        .file-upload-btn {
            display: inline-flex;
            align-items: center;
            gap: 8px;
            background: var(--primary);
            color: white;
            padding: 12px 20px;
            border-radius: 8px;
            font-weight: 600;
            cursor: pointer;
            transition: all 0.3s ease;
            border: none;
            height: 100%;
        }

        .file-upload-btn:hover {
            background: var(--primary-dark);
            transform: translateY(-2px);
        }

        #file-input {
            position: absolute;
            left: 0;
            top: 0;
            opacity: 0;
            width: 100%;
            height: 100%;
            cursor: pointer;
        }

        .stats {
            display: flex;
            gap: 15px;
            flex-wrap: wrap;
            margin-top: 15px;
        }

        .stat-card {
            background: var(--light);
            border-radius: 10px;
            padding: 15px 20px;
            display: flex;
            align-items: center;
            gap: 10px;
            min-width: 180px;
            box-shadow: var(--card-shadow);
            flex: 1;
        }

        .stat-icon {
            width: 40px;
            height: 40px;
            border-radius: 50%;
            display: flex;
            align-items: center;
            justify-content: center;
            font-size: 1.2rem;
        }

        .stat-content {
            display: flex;
            flex-direction: column;
        }

        .stat-value {
            font-size: 1.4rem;
            font-weight: 700;
        }

        .stat-label {
            font-size: 0.9rem;
            color: var(--gray);
        }

        .table-container {
            background: white;
            border-radius: 16px;
            box-shadow: var(--card-shadow);
            padding: 25px;
            overflow-x: auto;
            margin-bottom: 30px;
        }

        .table-header {
            display: flex;
            justify-content: space-between;
            align-items: center;
            margin-bottom: 20px;
            flex-wrap: wrap;
            gap: 15px;
        }

        .table-title {
            font-size: 1.5rem;
            font-weight: 600;
            color: var(--dark);
        }

        .search-box {
            display: flex;
            align-items: center;
            background: var(--light);
            border-radius: 8px;
            padding: 8px 15px;
            border: 1px solid var(--border);
        }

        .search-box input {
            border: none;
            background: transparent;
            padding: 5px 10px;
            font-size: 1rem;
            min-width: 215px;
        }

        .search-box input:focus {
            outline: none;
        }

        table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
        }

        thead {
            background: var(--light);
        }

        th {
            padding: 15px;
            text-align: left;
            font-weight: 600;
            color: var(--dark);
            border-bottom: 2px solid var(--border);
            position: relative;
            cursor: pointer;
        }

        th:hover {
            background: #f3f4f6;
        }

        .sort-icon {
            position: absolute;
            right: 10px;
            color: var(--gray);
            font-size: 0.8rem;
        }

        td {
            padding: 15px;
            border-bottom: 1px solid var(--border);
        }

        tr:last-child td {
            border-bottom: none;
        }

        tr:hover {
            background: rgba(79, 70, 229, 0.05);
        }

        .class-badge {
            display: inline-block;
            padding: 4px 12px;
            border-radius: 20px;
            font-size: 0.85rem;
            font-weight: 500;
            background: #DBEAFE;
            color: #1D4ED8;
        }

        .score-high {
            color: var(--success);
            font-weight: 600;
        }

        .score-medium {
            color: var(--warning);
        }

        .score-low {
            color: var(--danger);
        }

        .empty-state {
            text-align: center;
            padding: 60px 20px;
            color: var(--gray);
        }

        .empty-state i {
            font-size: 4rem;
            color: var(--light-gray);
            margin-bottom: 20px;
        }

        .empty-state h3 {
            font-size: 1.5rem;
            margin-bottom: 10px;
        }

        .empty-state p {
            font-size: 1.1rem;
            max-width: 500px;
            margin: 0 auto;
        }

        .file-requirements {
            background: #F3F4F6;
            border-radius: 8px;
            padding: 15px;
            margin-top: 20px;
            font-size: 0.9rem;
        }

        .file-requirements h4 {
            margin-bottom: 10px;
            display: flex;
            align-items: center;
            gap: 8px;
        }

        .file-requirements ul {
            padding-left: 20px;
        }

        .file-requirements li {
            margin-bottom: 5px;
        }

        footer {
            text-align: center;
            padding: 30px 0;
            color: var(--gray);
            margin-top: 20px;
        }

        .highlight {
            background-color: rgba(79, 70, 229, 0.1);
            font-weight: 600;
        }

        @media (max-width: 768px) {
            .header-top {
                flex-direction: column;
                align-items: flex-start;
            }

            .header-controls {
                width: 100%;
            }

            .form-group {
                width: 100%;
            }

            .stats {
                width: 100%;
            }

            .stat-card {
                min-width: 0;
            }

            th,
            td {
                padding: 10px;
                font-size: 0.85rem;
            }

            .table-header {
                flex-direction: column;
                align-items: flex-start;
            }

            .search-box {
                width: 100%;
            }

            .search-box input {
                width: 100%;
            }
        }
    </style>
</head>

<body>
    <div class="container">
        <header>
            <div class="header-top">
                <div class="logo">
                    <i class="fas fa-calculator"></i>
                    <span>数学周测成绩分析</span>
                </div>

                <div class="file-upload">
                    <button class="file-upload-btn">
                        <i class="fas fa-file-upload"></i>
                        上传Excel文件
                    </button>
                    <input type="file" id="file-input" accept=".xlsx, .xls" />
                </div>
            </div>

            <div class="control-group">
                <div class="form-group">
                    <label for="start-class"><i class="fas fa-layer-group"></i> 起始班级</label>
                    <input type="number" id="start-class" placeholder="请输入起始班级序号" min="1" />
                </div>

                <div class="form-group">
                    <label for="end-class"><i class="fas fa-layer-group"></i> 结束班级</label>
                    <input type="number" id="end-class" placeholder="请输入结束班级序号" min="1" />
                </div>

                <div class="form-group">
                    <label for="score-threshold"><i class="fas fa-bullseye"></i> 指定分数(>=)</label>
                    <input type="number" id="score-threshold" value="0" placeholder="默认为0" min="0" />
                </div>

                <div class="form-group">
                    <label for="sort-by"><i class="fas fa-sort"></i> 排序方式</label>
                    <select id="sort-by">
                        <option value="totalPoints">积分</option>
                        <option value="avgScore">平均分</option>
                        <option value="maxScore">最高分</option>
                        <option value="thresholdCount">指定分数人数</option>
                        <option value="excellentRate">优秀率</option>
                        <option value="passRate">及格率</option>
                    </select>
                </div>
            </div>

            <div class="stats">
                <div class="stat-card">
                    <div class="stat-icon" style="background: #DBEAFE; color: #1D4ED8;">
                        <i class="fas fa-users"></i>
                    </div>
                    <div class="stat-content">
                        <div class="stat-value" id="class-count">0</div>
                        <div class="stat-label">班级数量</div>
                    </div>
                </div>

                <div class="stat-card">
                    <div class="stat-icon" style="background: #D1FAE5; color: #047857;">
                        <i class="fas fa-chart-line"></i>
                    </div>
                    <div class="stat-content">
                        <div class="stat-value" id="avg-score">0.00</div>
                        <div class="stat-label">总平均分</div>
                    </div>
                </div>

                <div class="stat-card">
                    <div class="stat-icon" style="background: #FEF3C7; color: #B45309;">
                        <i class="fas fa-trophy"></i>
                    </div>
                    <div class="stat-content">
                        <div class="stat-value" id="top-score">0</div>
                        <div class="stat-label">最高分数</div>
                    </div>
                </div>

                <div class="stat-card">
                    <div class="stat-icon" style="background: #FEE2E2; color: #B91C1C;">
                        <i class="fas fa-exclamation-circle"></i>
                    </div>
                    <div class="stat-content">
                        <div class="stat-value" id="low-score">0</div>
                        <div class="stat-label">最低分数</div>
                    </div>
                </div>
            </div>
        </header>

        <div class="table-container">
            <div class="table-header">
                <h2 class="table-title">成绩分析结果</h2>
                <div class="search-box">
                    <i class="fas fa-search"></i>
                    <input type="text" id="search-input" placeholder="搜索班级..." />
                </div>
            </div>

            <div id="table-content">
                <div class="empty-state">
                    <i class="fas fa-file-excel"></i>
                    <h3>暂无分析数据</h3>
                    <p>请上传Excel文件以加载学生成绩数据</p>
                </div>
            </div>

            <div class="file-requirements">
                <h4><i class="fas fa-info-circle"></i> Excel文件要求：</h4>
                <ul>
                    <li>表格必须包含 <strong>班级</strong> 和 <strong>成绩</strong> 列</li>
                    <li>第一行应为表头，数据从第二行开始</li>
                    <li>支持 .xlsx 和 .xls 格式文件</li>
                    <li>成绩列应为数字格式，"缺考"将自动跳过</li>
                </ul>
            </div>
        </div>

        <footer>
            <p>© 2024 数学周测成绩分析系统 | 数学：zc-2024</p>
        </footer>
    </div>

    <script>
        document.getElementById('file-input').addEventListener('change', handleFile, false);
        document.getElementById('start-class').addEventListener('change', reprocessData, false);
        document.getElementById('end-class').addEventListener('change', reprocessData, false);
        document.getElementById('score-threshold').addEventListener('change', reprocessData, false);
        document.getElementById('sort-by').addEventListener('change', reprocessData, false);
        document.getElementById('search-input').addEventListener('input', filterClasses, false);

        let currentRows = [];
        let classData = {};
        let allClasses = [];

        function handleFile(event) {
            const file = event.target.files[0];
            if (!file) return;

            const reader = new FileReader();
            reader.onload = function (e) {
                const data = new Uint8Array(e.target.result);
                const workbook = XLSX.read(data, { type: 'array' });
                const sheet = workbook.Sheets[workbook.SheetNames[0]];
                currentRows = XLSX.utils.sheet_to_json(sheet, { header: 1 });
                reprocessData();
            };
            reader.readAsArrayBuffer(file);
        }

        function reprocessData() {
            const startClass = parseInt(document.getElementById('start-class').value) || 1;
            const endClass = parseInt(document.getElementById('end-class').value) || 100;
            const scoreThreshold = parseInt(document.getElementById('score-threshold').value) || 0;
            const sortBy = document.getElementById('sort-by').value;

            processData(currentRows, startClass, endClass, scoreThreshold, sortBy);
        }

        function filterClasses() {
            const searchTerm = document.getElementById('search-input').value.toLowerCase();
            if (!searchTerm) {
                updateTable(allClasses);
                return;
            }

            const filteredClasses = allClasses.filter(cls =>
                cls.className.toString().toLowerCase().includes(searchTerm)
            );

            updateTable(filteredClasses);
        }

        function processData(rows, startClass, endClass, scoreThreshold, sortBy) {
            if (rows.length === 0) {
                document.getElementById('table-content').innerHTML = `
                    <div class="empty-state">
                        <i class="fas fa-file-excel"></i>
                        <h3>暂无分析数据</h3>
                        <p>请上传Excel文件以加载学生成绩数据</p>
                    </div>
                `;
                return;
            }

            const header = rows[0];
            const classIndex = header.indexOf("班级");
            const scoreIndex = header.indexOf("成绩");

            if (classIndex === -1 || scoreIndex === -1) {
                alert('Excel文件中未找到"班级"或"成绩"列！');
                return;
            }

            classData = {};
            let totalScore = 0;
            let totalStudents = 0;
            let maxScore = -Infinity;
            let minScore = Infinity;
            let classSet = new Set();

            for (let i = 1; i < rows.length; i++) {
                const row = rows[i];
                const className = row[classIndex];
                const score = row[scoreIndex];

                if (className === undefined || (isNaN(score) || score === "缺考") || className < startClass || className > endClass) {
                    continue;
                }

                const numericScore = parseFloat(score);
                if (isNaN(numericScore)) continue;

                if (className < startClass || className > endClass) continue;

                classSet.add(className);

                if (!classData[className]) {
                    classData[className] = {
                        totalScore: 0,
                        count: 0,
                        nonZeroCount: 0,
                        maxScore: -Infinity,
                        minScore: Infinity,
                        thresholdCount: 0,
                        excellentCount: 0,
                        passCount: 0,
                        totalPoints: 0,
                        segmentCounts: {
                            '120': 0,
                            '[114,120)': 0,
                            '[108,114)': 0,
                            '[102,108)': 0,
                            '[96,102)': 0,
                            '[84,96)': 0,
                            '[72,84)': 0,
                            '[60,72)': 0,
                            '[48,60)': 0,
                            '[0,48)': 0
                        }
                    };
                }

                classData[className].totalScore += numericScore;
                classData[className].count += 1;
                classData[className].maxScore = Math.max(classData[className].maxScore, numericScore);
                classData[className].minScore = Math.min(classData[className].minScore, numericScore);

                if (numericScore != 0) {
                    classData[className].nonZeroCount += 1;
                }

                if (numericScore >= scoreThreshold) {
                    classData[className].thresholdCount += 1;
                }

                if (numericScore >= 96) {
                    classData[className].excellentCount += 1;
                }

                if (numericScore >= 72) {
                    classData[className].passCount += 1;
                }

                // 更新全局统计
                totalScore += numericScore;
                totalStudents++;
                maxScore = Math.max(maxScore, numericScore);
                minScore = Math.min(minScore, numericScore);

                // 计算积分
                if (numericScore == 120) {
                    classData[className].totalPoints += 15;
                    classData[className].segmentCounts['120'] += 1;
                } else if (numericScore >= 114) {
                    classData[className].totalPoints += 14;
                    classData[className].segmentCounts['[114,120)'] += 1;
                } else if (numericScore >= 108) {
                    classData[className].totalPoints += 12;
                    classData[className].segmentCounts['[108,114)'] += 1;
                } else if (numericScore >= 102) {
                    classData[className].totalPoints += 10;
                    classData[className].segmentCounts['[102,108)'] += 1;
                } else if (numericScore >= 96) {
                    classData[className].totalPoints += 8;
                    classData[className].segmentCounts['[96,102)'] += 1;
                } else if (numericScore >= 84) {
                    classData[className].totalPoints += 6;
                    classData[className].segmentCounts['[84,96)'] += 1;
                } else if (numericScore >= 72) {
                    classData[className].totalPoints += 4;
                    classData[className].segmentCounts['[72,84)'] += 1;
                } else if (numericScore >= 60) {
                    classData[className].totalPoints += 2;
                    classData[className].segmentCounts['[60,72)'] += 1;
                } else if (numericScore >= 48) {
                    classData[className].totalPoints += 1;
                    classData[className].segmentCounts['[48,60)'] += 1;
                } else if (numericScore >= 0) {
                    classData[className].segmentCounts['[0,48)'] += 1;
                }
            }

            // 更新统计卡片
            document.getElementById('class-count').textContent = classSet.size;
            document.getElementById('avg-score').textContent = totalStudents > 0 ? (totalScore / totalStudents).toFixed(2) : '0.00';
            document.getElementById('top-score').textContent = maxScore !== -Infinity ? maxScore : '0';
            document.getElementById('low-score').textContent = minScore !== Infinity ? minScore : '0';

            // 计算班级平均分
            const classAvg = [];
            for (const className in classData) {
                const avgScore = classData[className].nonZeroCount > 0 ?
                    classData[className].totalScore / classData[className].nonZeroCount : 0;
                const excellentRate = (classData[className].excellentCount / classData[className].count) * 100;
                const passRate = (classData[className].passCount / classData[className].count) * 100;

                classAvg.push({
                    className,
                    avgScore,
                    maxScore: classData[className].maxScore,
                    minScore: classData[className].minScore,
                    thresholdCount: classData[className].thresholdCount,
                    excellentCount: classData[className].excellentCount,
                    excellentRate,
                    passCount: classData[className].passCount,
                    passRate,
                    totalPoints: classData[className].totalPoints,
                    segmentCounts: classData[className].segmentCounts
                });
            }

            // 排序
            classAvg.sort((a, b) => {
                switch (sortBy) {
                    case 'avgScore': return b.avgScore - a.avgScore;
                    case 'maxScore': return b.maxScore - a.maxScore;
                    case 'thresholdCount': return b.thresholdCount - a.thresholdCount;
                    case 'excellentRate': return b.excellentRate - a.excellentRate;
                    case 'passRate': return b.passRate - a.passRate;
                    case 'totalPoints': return b.totalPoints - a.totalPoints;
                    default: return b.totalPoints - a.totalPoints;
                }
            });

            // 排名
            classAvg.forEach((item, index) => {
                item.rank = index + 1;
            });

            allClasses = classAvg;
            updateTable(classAvg);
        }

        function updateTable(classAvg) {
            if (classAvg.length === 0) {
                document.getElementById('table-content').innerHTML = `
                    <div class="empty-state">
                        <i class="fas fa-user-slash"></i>
                        <h3>没有符合条件的数据</h3>
                        <p>请尝试更改筛选条件或搜索关键词</p>
                    </div>
                `;
                return;
            }

            let tableHTML = `
                <table id="result-table">
                    <thead>
                        <tr>
                            <th>班级 <i class="fas fa-sort sort-icon"></i></th>
                            <th>平均分 <i class="fas fa-sort sort-icon"></i></th>
                            <th>积分 <i class="fas fa-sort sort-icon"></i></th>
                            <th>排名 <i class="fas fa-sort sort-icon"></i></th>
                            <th>最高分 <i class="fas fa-sort sort-icon"></i></th>
                            <th>最低分 <i class="fas fa-sort sort-icon"></i></th>
                            <th>超过指定分数 <i class="fas fa-sort sort-icon"></i></th>
                            <th>优秀人数 <i class="fas fa-sort sort-icon"></i></th>
                            <th>优秀率 <i class="fas fa-sort sort-icon"></i></th>
                            <th>及格人数 <i class="fas fa-sort sort-icon"></i></th>
                            <th>及格率 <i class="fas fa-sort sort-icon"></i></th>
                            <th>120</th>
                            <th>[114,120)</th>
                            <th>[108,114)</th>
                            <th>[102,108)</th>
                            <th>[96,102)</th>
                            <th>[84,96)</th>
                            <th>[72,84)</th>
                            <th>[60,72)</th>
                            <th>[48,60)</th>
                            <th>[0,48)</th>
                        </tr>
                    </thead>
                    <tbody>
            `;

            classAvg.forEach(item => {
                const isTop3 = item.rank <= 3;

                tableHTML += `
                    <tr ${isTop3 ? 'class="highlight"' : ''}>
                        <td><span class="class-badge">${item.className}</span></td>
                        <td>${item.avgScore.toFixed(2)}</td>
                        <td>${item.totalPoints}</td>
                        <td>${item.rank}</td>
                        <td class="score-high">${item.maxScore}</td>
                        <td class="score-low">${item.minScore}</td>
                        <td>${item.thresholdCount}</td>
                        <td>${item.excellentCount}</td>
                        <td>${item.excellentRate.toFixed(2)}%</td>
                        <td>${item.passCount}</td>
                        <td>${item.passRate.toFixed(2)}%</td>
                        <td>${item.segmentCounts['120']}</td>
                        <td>${item.segmentCounts['[114,120)']}</td>
                        <td>${item.segmentCounts['[108,114)']}</td>
                        <td>${item.segmentCounts['[102,108)']}</td>
                        <td>${item.segmentCounts['[96,102)']}</td>
                        <td>${item.segmentCounts['[84,96)']}</td>
                        <td>${item.segmentCounts['[72,84)']}</td>
                        <td>${item.segmentCounts['[60,72)']}</td>
                        <td>${item.segmentCounts['[48,60)']}</td>
                        <td>${item.segmentCounts['[0,48)']}</td>
                    </tr>
                `;
            });

            tableHTML += `
                    </tbody>
                </table>
            `;

            document.getElementById('table-content').innerHTML = tableHTML;

            // 添加表头排序功能
            document.querySelectorAll('th').forEach((th, index) => {
                th.addEventListener('click', () => {
                    const sortBy = ['className', 'avgScore', 'totalPoints', 'rank', 'maxScore',
                        'minScore', 'thresholdCount', 'excellentCount', 'excellentRate',
                        'passCount', 'passRate'][index];
                    if (sortBy) {
                        document.getElementById('sort-by').value = sortBy;
                        reprocessData();
                    }
                });
            });
        }

        // 初始化示例数据
        function initDemoData() {
            // const demoData = [
            //     ["班级", "成绩"],
            //     [1, 112],
            //     [1, 118],
            //     [1, 105],
            //     [1, 98],
            //     [1, 120],
            //     [2, 102],
            //     [2, 115],
            //     [2, 108],
            //     [2, 92],
            //     [2, 85],
            //     [3, 120],
            //     [3, 119],
            //     [3, 116],
            //     [3, 110],
            //     [3, 104],
            //     [4, 95],
            //     [4, 88],
            //     [4, 78],
            //     [4, 65],
            //     [4, 72],
            //     [5, 105],
            //     [5, 98],
            //     [5, 112],
            //     [5, 120],
            //     [5, 107]
            // ];

            // currentRows = demoData;
            document.getElementById('start-class').value = 1;
            document.getElementById('end-class').value = 1000;
            reprocessData();
        }

        // 页面加载时初始化示例数据
        window.onload = initDemoData;
    </script>
</body>

</html>